const express = require('express');
const router = express.Router();
const con = require("../db")
const con2 = require("../db/mysql")
const md5 = require("md5")
/* GET home page. */

// 分页查询
router.get('/', async (req, res) => {
  const { page, limit,industry,state,compary } = req.query;
  let where = "";
  if (industry) {
    where += ` AND customer.industry="${industry}"`
  }
  if (state) {
    where += `AND customer.state="${state}" `
  }
  if (compary) {
    where += `AND customer LIKE '%${compary}%'`
  }
  const result = await con2.query(`
    SELECT * FROM customer WHERE 1 ${where} ORDER BY id DESC LIMIT ${limit} OFFSET ${(page - 1) * limit};
    SELECT COUNT(*) AS total FROM customer
 `);
  res.json({
    msg: "查询成功",
    code: 200,
    data: result[0],
    total: result[1][0].total,

  })
});
router.delete('/:id', (req, res) => {
  const { id } = req.params;
  con.query(`DELETE FROM customer WHERE id=?`, id, (err, result) => {
    if (err) throw err;

    if (result.affectedRows === 1) {
      res.json({
        msg: "删除成功",
        code: 200
      });
    } else {
      res.json({
        msg: "删除失败",
        code: 500
      });
    }
  });
});
// router.get('/findbyrid', (req, res, next) => {
//   const { rid } = req.query;
//   con.query(`SELECT * FROM manager WHERE rid=?`, rid, (err, result) => {
//     if (err) throw err;
//     if (result.length) {
//       res.json({
//         msg: "当前角色有用户",
//         code: 201
//       })
//     } else {
//       res.json({
//         msg: "当前角色无用户",
//         code: 202
//       })
//     }
//   })
// });

router.post('/', async (req, res) => {
  const { body } = req;
  const result = await con2.query(`
     
     INSERT INTO customer ( compary, industry, province, name, phone, title, state, mid, needs) 
     VALUES (?,?,?,?,?,?,?,?,?);

     `, [body.compary,body.industry,body.province,body.name,body.phone,body.title, body.state,body.mid,body.needs, ]);
  if (result.affectedRows === 1) {
    res.json({
      msg: "添加成功",
      code: 200
    });
  } else {
    res.json({
      msg: "添加失败",
      code: 500
    });
  }
});
router.put('/:id', (req, res) => {
  const { id } = req.params;
  const { compary,
    industry,
    province,
    name,
    phone,
    title,
    state,
    mid,
    needs} = req.body;

    con.query(`UPDATE customer SET 
    compary=?,
    industry=?,
    province=?,
    name=?,
    phone=?,
    title=?,
    state=?,
    mid=?,
    needs=? WHERE id=?`, [compary,
      industry,
      province,
      name,
      phone,
      title,
      state,
      mid,
      needs,
      id], (err, result) => {
        if (err) throw err;
        if (result.affectedRows === 1) {
          res.json({
            msg: "更新成功",
            code: 200
          })
        } else {
          res.json({
            msg: "更新失败",
            code: 500
          })
        }
      })
    
  
});
module.exports = router;
